import 'dart:io' show Platform;
import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:path/path.dart';
import '../models/ssh_connection.dart';
import '../models/command_history.dart';

class DatabaseService {
  static const _databaseName = 'clssh.db';
  static const _databaseVersion = 1;
  
  Database? _database;

  DatabaseService() {
    // 初始化数据库工厂
    if (Platform.isWindows || Platform.isLinux || Platform.isMacOS) {
      databaseFactory = databaseFactoryFfi;
      sqfliteFfiInit();
    }
  }

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    final documentsDirectory = await getDatabasesPath();
    final path = join(documentsDirectory, _databaseName);

    // 使用FFI数据库工厂
    final factory = Platform.isWindows || Platform.isLinux || Platform.isMacOS 
        ? databaseFactoryFfi 
        : databaseFactory;

    return await factory.openDatabase(
      path,
      options: OpenDatabaseOptions(
        version: _databaseVersion,
        onCreate: _onCreate,
      ),
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute('''
      CREATE TABLE ssh_connections (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        host TEXT NOT NULL,
        port INTEGER NOT NULL DEFAULT 22,
        username TEXT NOT NULL,
        password TEXT,
        privateKeyPath TEXT,
        passphrase TEXT,
        lastUsed TEXT NOT NULL,
        usageCount INTEGER NOT NULL DEFAULT 0
      )
    ''');

    await db.execute('''
      CREATE TABLE command_history (
        id TEXT PRIMARY KEY,
        connectionId TEXT NOT NULL,
        command TEXT NOT NULL,
        executedAt TEXT NOT NULL,
        output TEXT NOT NULL,
        exitCode INTEGER NOT NULL,
        FOREIGN KEY (connectionId) REFERENCES ssh_connections (id)
      )
    ''');
  }

  // SSH连接管理
  Future<List<SSHConnection>> getConnections() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'ssh_connections',
      orderBy: 'lastUsed DESC',
    );
    return maps.map((map) => SSHConnection.fromMap(map)).toList();
  }

  Future<void> saveConnection(SSHConnection connection) async {
    final db = await database;
    await db.insert(
      'ssh_connections',
      connection.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  Future<void> deleteConnection(String id) async {
    final db = await database;
    await db.delete(
      'ssh_connections',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<void> updateConnectionUsage(String id) async {
    final db = await database;
    final connection = await db.query(
      'ssh_connections',
      where: 'id = ?',
      whereArgs: [id],
    );
    
    if (connection.isNotEmpty) {
      final conn = SSHConnection.fromMap(connection.first);
      await db.update(
        'ssh_connections',
        conn.copyWith(
          lastUsed: DateTime.now(),
          usageCount: conn.usageCount + 1,
        ).toMap(),
        where: 'id = ?',
        whereArgs: [id],
      );
    }
  }

  // 命令历史管理
  Future<List<CommandHistory>> getCommandHistory(String connectionId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'command_history',
      where: 'connectionId = ?',
      whereArgs: [connectionId],
      orderBy: 'executedAt DESC',
      limit: 100,
    );
    return maps.map((map) => CommandHistory.fromMap(map)).toList();
  }

  Future<void> saveCommandHistory(CommandHistory history) async {
    final db = await database;
    await db.insert(
      'command_history',
      history.toMap(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }
}